﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using TAMS;

namespace CS352_Prj
{
    public partial class Details : System.Web.UI.Page
    {
        MySqlConnection conn;
        MySqlCommand sqlCommand;
        MySqlDataReader readVals;
        string strProvider = Resource1.databaseConnection;
        string id;
        string type;

        protected void Page_Load(object sender, EventArgs e)
        {
            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();

            // Get the type
            type = Request.QueryString["type"];
            id = Request.QueryString["id"];

            string strSQL;
            if (type == "student")
            {
                strSQL = "SELECT * FROM GradStudent S WHERE S.sid = " + id;

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                while (readVals.Read())
                {
                    NameLabel.Text = readVals.GetString("s_name");
                    IdLabel.Text = "" + readVals.GetInt32("sid");
                    BirthdayLabel.Text = "" + readVals.GetDateTime("s_birthDate").ToString("MMM d yyyy");
                    AgeLabel.Text = CalculateAge(readVals.GetDateTime("s_birthDate"));
                    MemberSLabel.Text = "" + readVals.GetDateTime("s_entryDate").ToString("MMM d yyyy");
                    E_mail.Text = "" + readVals.GetString("s_email");
                    E_mail.NavigateUrl = "" + readVals.GetString("s_email");
                    Image1.ImageUrl = readVals.GetString("s_url");
                }
                readVals.Close();

                strSQL = "SELECT * FROM Housing H, SResides SR WHERE SR.sid = " + id + " AND H.hid = SR.hid";

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                while (readVals.Read())
                {
                    HousingLabel.Text = readVals.GetString("h_building");
                    DoorNoLabel.Text = readVals.GetString("doorNo");
                    HTelNoLabel.Text = readVals.GetString("h_telNo");
                    ResidesSLabel.Text = "" + readVals.GetDateTime("sr_since").ToString("MMM d yyyy");
                }
                readVals.Close();

                strSQL = "SELECT * FROM Office O, SWorks SW WHERE SW.sid = " + id + " AND O.offcode = SW.offcode";

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                while (readVals.Read())
                {
                    OfficeLabel.Text = readVals.GetString("offcode");
                    BuildingLabel.Text = readVals.GetString("o_building");
                    OTelNoLabel.Text = readVals.GetString("o_telno");
                    WorksSLabel.Text = readVals.GetDateTime("sw_since").ToString("MMM d yyyy");
                }
                readVals.Close();

                strSQL = "SELECT * FROM TA WHERE TA.ta_sid = " + id;

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                if (readVals.HasRows)
                {
                    readVals.Close();
                    CoursesLabel.Text = "Courses Asisted: <br>";

                    strSQL = "SELECT * FROM SectionConsists SC, Educates E, Semester S WHERE E.ta_sid = " + id + " AND SC.secCorsId = E.secCorsId AND S.semId = SC.semId ";

                    sqlCommand = new MySqlCommand(strSQL, conn);
                    readVals = sqlCommand.ExecuteReader();

                    int i = 0;
                    CoursesLabel.Text += "<table id=\"maintable\">";
                    CoursesLabel.Text += "<thead> <tr> <td>CourseId</td> <td>Section</td> <td>Semester</td> </tr> </thead> <tbody>";
                    while (readVals.Read())
                    {
                        if (i % 2 != 0)
                            CoursesLabel.Text += "<tr class=\"alt\">";
                        else
                            CoursesLabel.Text += "<tr>";
                        CoursesLabel.Text += "<td>" + readVals.GetString("cid") + "</td>";
                        CoursesLabel.Text += "<td>" + readVals.GetInt32("sectionNo") + "</td>";
                        CoursesLabel.Text += "<td>" + readVals.GetString("season") + "-";
                        CoursesLabel.Text += "" + readVals.GetInt64("year") + "</td>";
                        CoursesLabel.Text += "</tr>";
                        i++;
                    }
                    CoursesLabel.Text += "</tbody> </table>";
                }
                else
                {
                    readVals.Close();

                    strSQL = "SELECT * FROM RA WHERE RA.ra_sid = " + id;

                    sqlCommand = new MySqlCommand(strSQL, conn);
                    readVals = sqlCommand.ExecuteReader();

                    if (readVals.HasRows)
                    {
                        readVals.Close();
                        CoursesLabel.Text = "Researches Asisted:<br>";

                        CoursesLabel.Text += "<table id=\"maintable\">";
                        CoursesLabel.Text += "<thead> <tr> <td>Research Topic</td> <td>Since</td> </tr> </thead> <tbody>";

                        strSQL = "SELECT * FROM RA, Involved I, Research R WHERE RA.ra_sid = " + id + " AND I.ra_sid = RA.ra_sid AND R.rid = I.rid";

                        sqlCommand = new MySqlCommand(strSQL, conn);
                        readVals = sqlCommand.ExecuteReader();

                        int k = 0;
                        while (readVals.Read())
                        {
                            if (k % 2 != 0)
                                CoursesLabel.Text += "<tr class=\"alt\">";
                            else
                                CoursesLabel.Text += "<tr>";
                            CoursesLabel.Text += "<td>" + readVals.GetString("about") + "</td>";
                            CoursesLabel.Text += "<td>" + readVals.GetDateTime("r_since").ToString("MMM d yyyy") + "</td>";
                            CoursesLabel.Text += "</tr>";
                            k++;
                        }
                        CoursesLabel.Text += "</tbody> </table>";
                    }
                }
            }
            else if (type == "staff")
            {
                string title = "";

                strSQL = "SELECT * FROM FMember F WHERE F.fmem_fsid = " + id;

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                if (readVals.HasRows)
                {
                    readVals.Read();
                    title = readVals.GetString("rank") + " ";
                }
                readVals.Close();

                strSQL = "SELECT * FROM Instructor I WHERE I.ins_fsid = " + id;

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                if (readVals.HasRows)
                {
                    readVals.Read();
                    bool doctor = readVals.GetBoolean("doctorate");
                    if (doctor)
                        title = "Dr. ";
                }
                readVals.Close();

                strSQL = "SELECT * FROM Staff S WHERE S.fsid = " + id;

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                while (readVals.Read())
                {
                    NameLabel.Text = title + readVals.GetString("fs_name");
                    IdLabel.Text = "" + readVals.GetInt32("fsid");
                    BirthdayLabel.Text = "" + readVals.GetDateTime("fs_birthDate").ToString("MMM d yyyy");
                    AgeLabel.Text = CalculateAge(readVals.GetDateTime("fs_birthDate"));
                    MemberSLabel.Text = "" + readVals.GetDateTime("fs_entryDate").ToString("MMM d yyyy");
                    E_mail.Text = "" + readVals.GetString("fs_email");
                    E_mail.NavigateUrl = "" + readVals.GetString("fs_email");
                    Image1.ImageUrl = readVals.GetString("fs_url");
                }
                readVals.Close();

                strSQL = "SELECT * FROM Housing H, FResides FR WHERE FR.fsid = " + id + " AND H.hid = FR.hid";

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                while (readVals.Read())
                {
                    HousingLabel.Text = readVals.GetString("h_building");
                    DoorNoLabel.Text = readVals.GetString("doorNo");
                    HTelNoLabel.Text = readVals.GetString("h_telNo");
                    ResidesSLabel.Text = "" + readVals.GetDateTime("fr_since").ToString("MMM d yyyy");
                }
                readVals.Close();

                strSQL = "SELECT * FROM Office O, FWorks FW WHERE FW.fsid = " + id + " AND O.offcode = FW.offcode";

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                while (readVals.Read())
                {
                    OfficeLabel.Text = readVals.GetString("offcode");
                    BuildingLabel.Text = readVals.GetString("o_building");
                    OTelNoLabel.Text = readVals.GetString("o_telno");
                    WorksSLabel.Text = readVals.GetDateTime("fw_since").ToString("MMM d yyyy");
                }
                readVals.Close();

                CoursesLabel.Text = "Courses Instructed: <br>";

                strSQL = "SELECT * FROM SectionConsists SC, Educates E, Semester S WHERE E.fsid = " + id + " AND SC.secCorsId = E.secCorsId AND S.semId = SC.semId ";

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                int i = 0;
                CoursesLabel.Text += "<table id=\"maintable\">";
                CoursesLabel.Text += "<thead> <tr> <td>CourseId</td> <td>Section</td> <td>Semester</td> </tr> </thead> <tbody>";
                while (readVals.Read())
                {
                    if (i % 2 != 0)
                        CoursesLabel.Text += "<tr class=\"alt\">";
                    else
                        CoursesLabel.Text += "<tr>";
                    CoursesLabel.Text += "<td>" + readVals.GetString("cid") + "</td>";
                    CoursesLabel.Text += "<td>" + readVals.GetInt32("sectionNo") + "</td>";
                    CoursesLabel.Text += "<td>" + readVals.GetString("season") + "-";
                    CoursesLabel.Text += "" + readVals.GetInt64("year") + "</td>";
                    CoursesLabel.Text += "</tr>";
                    i++;
                }
                CoursesLabel.Text += "</tbody> </table>";
                readVals.Close();

                // IF HE IS FMEMBER THEN SHOW HIS RESEARCH ALSO

                strSQL = "SELECT * FROM FMember F WHERE F.fmem_fsid = " + id;

                sqlCommand = new MySqlCommand(strSQL, conn);
                readVals = sqlCommand.ExecuteReader();

                if (readVals.HasRows)
                {
                    readVals.Close();
                    CoursesLabel.Text += "Researches:<br>";

                    CoursesLabel.Text += "<table id=\"maintable\">";
                    CoursesLabel.Text += "<thead> <tr> <td>Research Topic</td> <td>Since</td> </tr> </thead> <tbody>";

                    strSQL = "SELECT * FROM FMember F, Involved I, Research R WHERE F.fmem_fsid = " + id + " AND I.ra_sid = F.fmem_fsid AND R.rid = I.rid";

                    sqlCommand = new MySqlCommand(strSQL, conn);
                    readVals = sqlCommand.ExecuteReader();

                    int k = 0;
                    while (readVals.Read())
                    {
                        if (k % 2 != 0)
                            CoursesLabel.Text += "<tr class=\"alt\">";
                        else
                            CoursesLabel.Text += "<tr>";
                        CoursesLabel.Text += "<td>" + readVals.GetString("about") + "</td>";
                        CoursesLabel.Text += "<td>" + readVals.GetDateTime("r_since").ToString("MMM d yyyy") + "</td>";
                        CoursesLabel.Text += "</tr>";
                        k++;
                    }
                    CoursesLabel.Text += "</tbody> </table>";
                }
            }
            conn.Close();
        }

        protected string CalculateAge(DateTime date)
        {
            int i = DateTime.Now.Year - date.Year;
            string s = "" + i;
            return s;
        }
    }
}